package main;

import java.sql.*;
import java.util.Scanner;

/**
 * Created by freewheel on 2016/10/10.
 */
public class ManagerHelper {

    private static final int COUNT_SALESRECORD = 1;
    private static final int SHOW_MANUF_VALUES = 2;
    private static final int SHOW_POPULAR_PART = 3;
    private static final int RETURN_MAIN = 4;

    public static void printManagerOperationsMenu() {
        String menu = "-----Operations for manager menu-----\n" +
                "What kinds of operation would you like to perform?\n" +
                "1. Count the no. of sales record of each salesperson under a specific range on years of experience\n" +
                "2. Show the total sales value of each manufacturer\n" +
                "3. Show the N most popular part\n" +
                "4. Return to main menu\n" +
                "Enter your Choice: ";
        System.out.println(menu);
    }

    public static void performOperationsByOption(int option) {
        switch (option) {
            case COUNT_SALESRECORD: {
                countRecordsOfSalesperson();
                break;
            }
            case SHOW_MANUF_VALUES: {
                showManufacturerValues();
                break;
            }
            case SHOW_POPULAR_PART: {
                showPopularParts();
                break;
            }
            case RETURN_MAIN: {
                Entry.enterMainMenu();
                return;
            }
            default:
                System.out.println("\nPlease Input Number 1  to 4 !");
                break;
        }

        Entry.showMenuByRole(Role.MANAGER);

    }

    private static void showPopularParts() {
        System.out.println("Type in the Number of Parts : ");
        Scanner sc = Entry.getScanner();
        if (sc.hasNextInt()) {
            int numberOfPart = sc.nextInt();
            if (numberOfPart < 1) {
                System.out.println("WARNING: Please input integer larger than 0 !");
                showPopularParts();
            } else {
                showPopularParts(numberOfPart);
            }
        }
    }

    private static void showPopularParts(int numberOfPart) {
        System.out.println("| Part ID | Part Name | No. of Transaction | ");
        Connection conn = DBHelper.getConnection();
        Statement statement = null;
        try {
            if (!DBHelper.tableExist("part")) {
                System.out.println("WARNING: required table not exist !");
                return;
            }

            statement = conn.createStatement();
            String sql = "Select P.pID, P.pName, Temp.numOfT " +
                    "From part P, (Select P1.pID, COUNT(*) AS numOfT " +
                    "From part P1, transaction T " +
                    "Where T.pID = P1.pID " +
                    "Group By T.pID ) As Temp " +
                    "Where P.pID = Temp.pID AND Temp.numOfT > 0 " +
                    "Order By Temp.numOfT DESC, P.pID ASC " +
                    "Limit " + numberOfPart + " ;";
            ResultSet resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                System.out.println("| " + resultSet.getInt(1) +
                        " | " + resultSet.getString(2) +
                        " | " + resultSet.getInt(3) +
                        " | ");
            }

            resultSet.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void showManufacturerValues() {
        System.out.println("| Manufacturer ID | Manufacturer Name | Total Sales Value |");
        Connection conn = DBHelper.getConnection();
        Statement statement = null;
        try {
            if (!DBHelper.tableExist("manufacturer")) {
                System.out.println("WARNING: required table not exist !");
                return;
            }

            statement = conn.createStatement();
            String sql = "Select M.mID, M.mName, Temp.totalVal " +
                    "From manufacturer M, (Select M1.mID, SUM(P.pPrice) AS totalVal " +
                    "From manufacturer M1, part P, transaction T " +
                    "Where T.pID = P.pID AND P.mID = M1.mID " +
                    "Group By M1.mID) AS Temp " +
                    "Where M.mID = Temp.mID " +
                    "Order By Temp.totalVal DESC;";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println("| " + resultSet.getInt(1) +
                        " | " + resultSet.getString(2) +
                        " | " + resultSet.getInt(3) +
                        " |");
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void countRecordsOfSalesperson() {
        Scanner sc = Entry.getScanner();
        System.out.println("Type in the lower bound for years of experience: ");
        if (sc.hasNextInt()) {
            int lower = sc.nextInt();
            if (lower < 0) {
                System.out.println("WARNING: Please input integer larger than 0 !");
                countRecordsOfSalesperson();
            } else {
                System.out.println("Type in the higher bound for years of experience: ");
                if (sc.hasNextInt()) {
                    int higher = sc.nextInt();
                    if (higher < lower) {
                        System.out.println("WARNING: The higher bound should be larger than the lower bound ! ");
                        countRecordsOfSalesperson();
                    } else {
                        countRecordsOfSalesperson(lower, higher);
                    }
                }
            }
        }
    }

    private static void countRecordsOfSalesperson(int lower, int higher) {
        System.out.println("| ID | Name | Years of Experience | Number of Transaction | ");
        Connection conn = DBHelper.getConnection();
        String sql = "Select S.sID, S.sName, S.sExperience, TEMP.recordNum " +
                "From salesperson S, (Select S1.sID, COUNT(*) as recordNum  From salesperson S1, transaction T " +
                "Where S1.sExperience >= ? AND  S1.sExperience <= ? AND S1.sID = T.sID " +
                "Group By S1.sID) AS TEMP " +
                "Where  S.sID = TEMP.sID " +
                "Order By S.sID DESC;";

        PreparedStatement statement = null;
        try {
            if (!DBHelper.tableExist("salesperson")) {
                System.out.println("WARNING: required table not exist !");
                return;
            }

            statement = conn.prepareStatement(sql);
            statement.setInt(1, lower);
            statement.setInt(2, higher);

            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println("| " + resultSet.getInt(1) +
                        " | " + resultSet.getString(2) +
                        " | " + resultSet.getInt(3) +
                        " | " + resultSet.getInt(4) +
                        " | ");
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
